﻿using System.Linq;
using System.Data.Linq;
using UniMana.Data;
using System;
namespace UniMana.Data
{
    public static class Catalogue_MajorData
    {
        private static DBMain m_data = new DBMain();
        public static IQueryable<Catalogue_Major>
            GetListByRef(this Table<Catalogue_Major> mj,
                                int _iLvKey = -1,
                                int _idpKey = -1)
        {
            if (_idpKey != -1)
            {
                if (_iLvKey != -1)
                {
                    return mj.Where(p => p.Catalogue_DepartmentKey ==
                        _idpKey && p.Catalogue_LevelKey == _iLvKey).Distinct();
                }
                else
                {
                    return mj.Where(p => p.Catalogue_DepartmentKey == _idpKey).Distinct();
                }
            }
            else
            {
                if (_iLvKey != -1)
                {
                    return mj.Where(p => p.Catalogue_LevelKey == _iLvKey).Distinct();
                }
                else
                {
                    return mj.Distinct();
                }
            }
        }


        public static IQueryable GetListByRef(
            object _objLevelKey=null, 
            object _objDepartmentKey=null)
        {
            var iQuery = from p in m_data.m_DCReadOnly.Catalogue_Majors select p;
            if (_objLevelKey != null)
            {
                iQuery = iQuery.Where(p => p.Catalogue_LevelKey == int.Parse(_objLevelKey.ToString()));
            }
            if (_objDepartmentKey != null)
            {
                iQuery = iQuery.Where(p => p.Catalogue_DepartmentKey == int.Parse(_objDepartmentKey.ToString()));
            }

            return iQuery;

        }

        public static IQueryable GetListByDepartMent(
                                          this Table<Catalogue_Major> nCatalogue_Major,
                                               Table<Catalogue_Level> nCatalogue_Level,
                                               int _iDepartmentKey =-1)
        {
                var IQuery = from mj in nCatalogue_Major
                             join le in nCatalogue_Level on mj.Catalogue_LevelKey equals le.Catalogue_LevelKey
                             where
                               mj.Catalogue_DepartmentKey == _iDepartmentKey
                             select new
                             {
                                 mj.Catalogue_MajorKey,
                                 mj.Catalogue_DepartmentKey,
                                 mj.Catalogue_LevelKey,
                                 mj.ID,
                                 Name = mj.Name + " - " + le.Name
                             };
                return IQuery.Distinct();
        }

        public static IQueryable GetListByDepartMent(
                                        this Table<Catalogue_Major> nCatalogue_Major,
                                        int _iDepartmentKey)
        {
            return nCatalogue_Major.Where(p => p.Catalogue_DepartmentKey == _iDepartmentKey);
        }

        public static IQueryable GetMajorByDepartment(this Table<Catalogue_Major> major,
            int departmentkey)
        {
            var IQuery = from mj in major
                         where
                           mj.Catalogue_DepartmentKey == departmentkey
                         select mj;
            return IQuery.Distinct();
        }

        #region [An]
        /// <summary>
        /// Lấy ngành có trong chương trình đào tạo
        /// </summary>
        /// <param name="nMajor"></param>
        /// <param name="nTrainingProgram"></param>
        /// <returns></returns>

        public static IQueryable GetMajorInTraingProgram(this Table<Catalogue_Major> nMajor,
            Table<Training_TrainingProgram> nTrainingProgram,
            Table<Catalogue_Level> nCatalogue_Level)
        {
            var major = (from t in nTrainingProgram
                         join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                         join t1 in nCatalogue_Level on t0.Catalogue_LevelKey equals t1.Catalogue_LevelKey
                         select new
                         {
                             t.Catalogue_MajorKey,
                             t0.ID,
                             t0.Name,
                             t0.Catalogue_LevelKey,
                             LevelName = t1.Name,
                             t0.Catalogue_DepartmentKey
                         }).Distinct();
            return major.Distinct();
        }


        /// <summary>
        /// Lấy mã khoa với mã ngành
        /// </summary>
        /// <param name="nCatalogue_Major"></param>
        /// <param name="m_iMajorKey"></param>
        /// <returns></returns>

        public static int GetDepartmentKeyWithMajorKey(this Table<Catalogue_Major> nCatalogue_Major, int m_iMajorKey)
        {
            var values = from t in nCatalogue_Major
                         where t.Catalogue_MajorKey == m_iMajorKey
                         select t;
            if (values.Count() > 0)
                return int.Parse(values.FirstOrDefault().Catalogue_DepartmentKey.ToString());
            else
                return 0;
        }

        /// <summary>
        /// Lấy danh sách ngành và bậc đào tạo
        /// </summary>
        /// <param name="nCatalogue_Major"></param>
        /// <param name="nCatalogue_Level"></param>
        /// <param name="m_iDepartmentKey"></param>
        /// <returns></returns>

        public static IQueryable GetMajorLevel(this Table<Catalogue_Major> nCatalogue_Major,
            Table<Catalogue_Level> nCatalogue_Level,
            int m_iDepartmentKey)
        {
            var values = from t in nCatalogue_Major
                         join t1 in nCatalogue_Level on t.Catalogue_LevelKey equals t1.Catalogue_LevelKey
                         select new
                         {
                             t.Catalogue_MajorKey,
                             t.ID,
                             t.Name,
                             t.Catalogue_LevelKey,
                             LevelName = t1.Name,
                             t.Catalogue_DepartmentKey
                         };
            if (m_iDepartmentKey > 0)
                values = values.Where(tb => tb.Catalogue_DepartmentKey == m_iDepartmentKey);
            return values.Distinct();
        }

        /// <summary>
        /// Lấy ngành với khoa
        /// </summary>
        /// <param name="nMajor"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="m_iDepartment"></param>
        /// <returns></returns>

        public static IQueryable GetMajorByDepartment(this Table<Catalogue_Major> nMajor,
            Table<Training_TrainingProgram> nTrainingProgram,
            Table<Catalogue_Level> nCatalogue_Level,
            int m_iDepartment)
        {
            var major = (from t in nTrainingProgram
                         join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                         join t1 in nCatalogue_Level on t0.Catalogue_LevelKey equals t1.Catalogue_LevelKey
                         where
                           t0.Catalogue_DepartmentKey == m_iDepartment
                         select new
                         {
                             t.Catalogue_MajorKey,
                             t0.ID,
                             t0.Name,
                             t0.Catalogue_LevelKey,
                             LevelName = t1.Name,
                         }).Distinct();
            return major.Distinct();
        }

        /// <summary>
        /// Lấy ngành với khoa, bậc đào tạo
        /// </summary>
        /// <param name="nMajor"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="m_iTrainingProgram"></param>
        /// <param name="m_iLevel"></param>
        /// <returns></returns>

        public static IQueryable GetMajorKeyByDepartmentLevel(this Table<Catalogue_Major> nMajor,
            Table<Training_TrainingProgram> nTrainingProgram,
            int m_iDepartment,
            int m_iLevel)
        {
            var major = (from t in nTrainingProgram
                         join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                         where
                           t.Catalogue_LevelKey == m_iLevel &&
                           t0.Catalogue_DepartmentKey == m_iDepartment
                         select new
                         {
                             t.Catalogue_MajorKey
                         }).Distinct();
            return major.Distinct();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nMajor"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="m_iMajor"></param>
        /// <returns></returns>

        public static IQueryable GetDepartmentKeyByMajor(this Table<Catalogue_Major> nMajor,
            Table<Training_TrainingProgram> nTrainingProgram,
            int m_iMajor)
        {
            var department = (from t in nMajor
                              join t0 in nTrainingProgram on new { Catalogue_MajorKey = t.Catalogue_MajorKey } equals new { Catalogue_MajorKey = Convert.ToInt32(t0.Catalogue_MajorKey) }
                              where
                                t0.Catalogue_MajorKey == m_iMajor
                              select new
                              {
                                  t.Catalogue_DepartmentKey
                              }).Distinct();
            return department.Distinct();
        }

        // <summary>
        /// Lấy bậc đào tạo với khoa, ngành
        /// </summary>
        /// <param name="nMajor"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="m_iTrainingProgram"></param>
        /// <param name="m_iMajor"></param>
        /// <returns></returns>

        public static IQueryable GetLevelKeyByDepartmentMajor(this Table<Catalogue_Major> nMajor,
            Table<Training_TrainingProgram> nTrainingProgram,
            int m_iDepartment,
            int m_iMajor)
        {
            var level = (from t in nTrainingProgram
                         join t0 in nMajor on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                         where
                           t0.Catalogue_DepartmentKey == m_iDepartment &&
                           t.Catalogue_MajorKey == m_iMajor
                         select new
                         {
                             t.Catalogue_LevelKey
                         }).Distinct();
            return level.Distinct();
        }

        /// <summary>
        /// Lấy ngành với Khoa
        /// </summary>
        /// <param name="nMajor"></param>
        /// <param name="_iDepartmenKey"></param>
        /// <returns></returns>
        public static IQueryable GetListByDepartmentKey(this Table<Catalogue_Major> nCatalogue_Major,
            Table<Catalogue_Level> nCatalogue_Level,
            int _iDepartmenKey)
        {
            var values = (from t0 in nCatalogue_Major
                          join t1 in nCatalogue_Level on t0.Catalogue_LevelKey equals t1.Catalogue_LevelKey
                          select new
                          {
                              t0.Catalogue_MajorKey,
                              t0.ID,
                              t0.Name,
                              t0.Catalogue_LevelKey,
                              LevelName = t1.Name,
                              t0.Catalogue_DepartmentKey
                          }).Distinct();

            if (_iDepartmenKey > 0)
            {
                values = values.Where(tb => tb.Catalogue_DepartmentKey == _iDepartmenKey);
            }
            return values;
        }

        #endregion

        #region [Rpt - Thảo ]
        public static IQueryable Student_Major(this  Table<Catalogue_Major> cataMajor,
              Table<Training_Student_Class> student_Class,
              Table<Training_Student> student,
              Table<Catalogue_Course> cataCourse, int _iMajorKey)
        {
            var view = from t in cataMajor
                       join t0 in student_Class on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                       join t1 in cataCourse on new { Catalogue_CourseKey = Convert.ToInt32(t0.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t1.Catalogue_CourseKey }
                       group new { t, t1 } by new
                       {
                           t.Catalogue_MajorKey,
                           t.ID,
                           t.Name,
                           t1.Catalogue_CourseKey,
                           Column1 = t1.Name
                       } into g
                       where g.Key.Catalogue_MajorKey == _iMajorKey
                       orderby
                         g.Key.Column1
                       select new
                       {
                           Catalogue_CourseKey = (System.Int32?)g.Key.Catalogue_CourseKey,
                           TenKhoaHoc = g.Key.Column1,
                           Catalogue_MajorKey = (System.Int32?)g.Key.Catalogue_MajorKey,
                           MaNganh = g.Key.ID,
                           TenNganh = g.Key.Name,
                           TongSoSV = (Int64?)g.Count()
                       };
            return view;
        }
        #endregion
    }
}